<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
 <%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%@ page import = "java.sql.PreparedStatement" %>

<%
//Get session
	String strUser = String.valueOf(session.getAttribute("sUser"));

//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	
	
		System.out.println("Active curriculum_import_background !!");

		String file_pathx = request.getParameter("file_path");
		System.out.println("File_path background:"+file_pathx);
		int count = 0;
		int i = 0 ;
		int num_row = 0;
		int num_col = 0;
		
		String courseID = "";
		String nameEN = "";
		
		String tempcredit="";
		String[] split;
		
		String credit = "" ;
		int lecture = 0 ;
		int lab = 0 ;
		int self = 0;
		String detail = "";
		int course_add = 0;
		
		List<String> fail_list = new ArrayList<String>();
		
		
		java.util.Date dt = new java.util.Date();

		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");

		String date_now = sdf.format(dt);
		
		
		
		
		String[] header_array = new  String[]{"รหัสวิชา","ชื่อวิชาภาษาอังกฤษ","หน่วยกิต","บรรยาย","ปฏิบัติ","ค้นคว้า","รายละเอียด"};
		System.out.println("//////////////////////////////////////////////////////////////////: ");
		System.out.println("Header_array size : "+ header_array.length);
		
		try{
			FileInputStream file = new FileInputStream(new File(file_pathx));
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			 
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            
            Iterator<Row> headerIt_row_check = sheet.iterator();
            Row row_check = headerIt_row_check.next();
            Iterator<Cell> headerIt_cell_check = row_check.cellIterator();
            
            while (rowIterator.hasNext())
            {	
            	System.out.println(" //////////  ");
            	if(count==0)
            	{
            		while(headerIt_cell_check.hasNext()){
            			Cell header_cell = headerIt_cell_check.next();
            			   switch (header_cell.getCellType())
                           {
                               case Cell.CELL_TYPE_NUMERIC:
                                	//System.out.print(header_cell.getNumericCellValue() + " ");
                                	 // System.out.print("NUMERIC!");
                                   break;
                               case Cell.CELL_TYPE_STRING:
                                   System.out.print(header_cell.getStringCellValue()+ " ");
                                   if(header_array[i].equals(header_cell.getStringCellValue()))
	                               	 {
	                               		 System.out.println("Pass:" + i);
	                               	 }
	                               	 else{
	                               		 
	                               		throw new Exception();
	                               		
	                               	 }
                             	 
                                 break;
                         }
          			   i++;
            			
            		}
            		System.out.println("");
            		count = count +1;
            		rowIterator.next();
            		System.out.println("////////// END Header////////////");
            	}
            	
            	try{
            		            		
	                Row row = rowIterator.next();
	              
	             	
	                //For each row, iterate through all the columns
	                Iterator<Cell> cellIterator = row.cellIterator();
	                while (cellIterator.hasNext())
	                {
	                	
	                    Cell cell = cellIterator.next();
	                    //Check the cell type and format accordingly
	                    	
			                 	switch(num_col){
			               		case 0 :
			               			courseID = cell.getStringCellValue();
				      						System.out.println("courseID: "+courseID);
			               			break;			               		
			               			
			               		case 1 :
			               			nameEN = cell.getStringCellValue();
			               					System.out.println("nameEN:"+nameEN);
			               			break;
			               		case 2 :
			               			tempcredit = cell.getStringCellValue();
			               			split = tempcredit.split(" ");
			               			credit = split[0];
			       							System.out.println("credit:"+credit);
			               			break;
			               		case 3 :
			               			lecture = (int)cell.getNumericCellValue();
			               					System.out.println("lecture:"+lecture);			               					
			               			break;
			               		case 4 :
			               			lab  = (int)cell.getNumericCellValue();
			               					System.out.println("lab:"+lab);
			               			break;
			               		case 5 :
			               			self = (int)cell.getNumericCellValue();			               					
			               					System.out.println("self :"+self);
			               			break;
			               		case 6 :
			               			detail = cell.getStringCellValue();
			              					System.out.println("detail:"+detail);
			               			break;			               		
			                 }
	                  	                    	 
		                num_col++;	 
	                }
	                num_col = 0;
	                num_row ++;

						///////////////////Phase 1 // Check Existing data in course table 
									ResultSet rs_course =	connect.createStatement().executeQuery(" SELECT * "
																					+ " FROM course_name "
																					+ " WHERE course_id='" + courseID +"' AND course_name_en='"+ nameEN + "' AND credit='"+ credit +"' AND lecture_hour="+ lecture +" AND lab_hour="+ lab +" AND self_learning_hour="+ self);
									rs_course.last();
									
									if(rs_course.getRow()==1)
									{
										System.out.println("This course already exist in course_name table");
										
										}else{	
											String tempName = "-";
											connect.createStatement().executeUpdate("INSERT INTO `course_name`(`course_id`,`course_name_th`,"+
													" `course_name_en`, `credit`, `lecture_hour`, `lab_hour`,`self_learning_hour`, `detail`)"
													+ " VALUES ('"+courseID+"','"+tempName+"','" + nameEN + "', '" + credit + "', "
													+ lecture + ", " + lab +", "+ self + ", '" + detail + "')" );
											
											String sql_course_name = "INSERT INTO `course_name`(`course_id`,`course_name_th`,"+
													" `course_name_en`, `credit`, `lecture_hour`, `lab_hour`,`self_learning_hour`, `detail`)"
													+ " VALUES ('"+courseID+"','"+tempName+"','" + nameEN + "', '" + credit + "', "
													+ lecture + ", " + lab +", "+ self + ", '" + detail + "')";
											
											/* Log file */
											String log_user = strUser+" add course_name";
											Log.writeUserFile(log_user);
											Log.writeUserFile(sql_course_name);
											
											System.out.println("courseID: "+courseID);
											System.out.println("nameEN: "+nameEN);
											System.out.println("credit: "+credit);
											System.out.println("lecture: "+lecture);
											System.out.println("lab: "+lab);
											System.out.println("self: "+self);
											System.out.println("detail: "+detail);
											
											course_add++;
										}	// END if Phase 1 
									
					            	} catch(Exception getData){ /// try row ite
					            		System.out.println("Error cant't read Excel:"+getData);
					            		num_col = 0;
					            		fail_list.add(num_row+1+"");
					            		num_row++;
					            	}
			                
			                
			            } // END while(rowIterator.hasNext())
			            	
			    	
	            file.close();
	            System.out.println("Add size :"+course_add);	            			
	            System.out.println("fail_list size :"+fail_list.size());
					            			
	            out.print("Successfully Added "+course_add+" file(s) to course table and "+fail_list.size()+" file(s) are failed to add");
					            			
	            for(int num_fail= 0;num_fail<fail_list.size();num_fail++)
	            {
	                    	out.print("\nFail to import Excel's row: "+fail_list.get(num_fail).toString());
	            }	            
				
			}
			catch(Exception x){
				System.out.println(x);
				
				out.print("fail");
			}

		connect.close();

	%>